﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

public partial class ColumnToProterty : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable dt = getTableDT();
        StringBuilder pa = new StringBuilder();
        StringBuilder pro = new StringBuilder();
        foreach (DataRow dr in dt.Rows)
        {
            pa.Append(string.Format(this.TextBox2.Text, dr["欄位名稱"]));
            pro.Append(string.Format(this.TextBox3.Text, dr["欄位名稱"]+":"+dr["欄位備註"], dr["欄位名稱"]));
			
			// for fhm
			//pro.Append(string.Format(this.TextBox3.Text, dr["欄位名稱"], dr["欄位名稱"]));
        }

        TextBox4.Text = pa.ToString();
        TextBox4.Text += pro.ToString();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        DataTable dt = getTableDT();
        Label1.Text = "";
        foreach (DataRow dr in dt.Rows)
        {
            Label1.Text += (dr["欄位名稱"] + ", ");
        }

    }

    public DataTable getTableDT()
    {
        SqlConnection conn = new SqlConnection(Session["CONNSTR"].ToString());
        //        string cmdText = "select * from information_schema.columns where table_name=@table_name";
        string cmdText = "SELECT  a.COLUMN_NAME as 欄位名稱, a.DATA_TYPE as 資料型別, ( SELECT value FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', default) WHERE name='MS_Description' and objtype='COLUMN' and objname Collate Chinese_Taiwan_Stroke_CI_AS = a.COLUMN_NAME ) as 欄位備註 FROM INFORMATION_SCHEMA.COLUMNS a WHERE table_name=@table_name ";
		
		// for fhm
		//string cmdText = "SELECT  a.COLUMN_NAME as 欄位名稱, a.DATA_TYPE as 資料型別  FROM INFORMATION_SCHEMA.COLUMNS a WHERE table_name=@table_name ";
        SqlCommand cmd = new SqlCommand(cmdText, conn);
        cmd.Parameters.AddWithValue("@table_name", this.Tables1.SelectedValue);
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
    }
}
